﻿/**
@file
    TableContents.cs
@brief
    Copyright 2009 Creative Crew. All rights reserved.
@author
    William Chang
@version
    0.1
@date
    - Created: 2008-09-05
    - Modified: 2009-04-21
    .
@note
    References:
    - General:
        - http://en.wikipedia.org/wiki/C_Sharp_(programming_language)
        .
    .
*/

using System;
using System.Data;
using System.Collections.Generic;
using System.Web;

namespace ent {

/// <summary>Class TableContents extends TableCommon</summary>
public class TableContents : TableCommon {

#region Fields

    public const String TBL__contents = "contents";
    public const String TBL__contents___PK__content_id = "content_id";
    public const String TBL__contents__content_id = "content_id";
    public const String TBL__contents__content_type = "content_type";
    public const String TBL__contents__content_parent_id = "content_parent_id";
    public const String TBL__contents__content_assignedto = "content_assignedto";
    public const String TBL__contents__content_author_user_id = "content_author_user_id";
    public const String TBL__contents__content_editor_user_id = "content_editor_user_id";
    public const String TBL__contents__content_title = "content_title";
    public const String TBL__contents__content_title_slug = "content_title_slug";
    public const String TBL__contents__content_header = "content_header";
    public const String TBL__contents__content_body = "content_body";
    public const String TBL__contents__content_footer = "content_footer";
    public const String TBL__contents__content_status = "content_status";
    public const String TBL__contents__content_style_id = "content_style_id";
    public const String TBL__contents__content_template_id = "content_template_id";
    public const String TBL__contents__content_order = "content_order";
    public const String TBL__contents__content_order_type = "content_order_type";
    public const String TBL__contents__content_order_menu = "content_order_menu";
    public const String TBL__contents__content_date_modified = "content_date_modified";
    public const String TBL__contents__content_date_published = "content_date_published";
    public const String TBL__contents__content_date_created = "content_date_created";
    public const String TBL__contents__content_deleted = "content_deleted";

    public const String TBL__contents_hierarchy__sort_id = "sort_id";
    public const String TBL__contents_hierarchy__content_id = "content_id";
    public const String TBL__contents_hierarchy__content_parent_id = "content_parent_id";
    public const String TBL__contents_hierarchy__content_level = "content_level";
    public const String TBL__contents_hierarchy__content_key = "content_key";
    public const String TBL__contents_hierarchy__content_title = "content_title";
    public const String TBL__contents_hierarchy__content_title_slug = "content_title_slug";
    public const String TBL__contents_hierarchy__content_assignedto = "content_assignedto";

    public const String VW__contents = "vw_contents";
    public const String VW__contents_extended = "vw_contents_extended";
    public const String VW__contents_hierarchy = "vw_contents_hierarchy";

#endregion    
    
    /// <summary>Default constructor.</summary>
    public TableContents() {}
    /// <summary>Get relevant columns for user.</summary>
    public static System.Collections.SortedList getColumnsRelevantPosts() {
        System.Collections.SortedList p = new System.Collections.SortedList();

        p.Add(1, TBL__contents__content_id);
        p.Add(2, TBL__contents__content_title);
        p.Add(3, TBL__contents__content_status);
        p.Add(4, TBL__contents__content_parent_id);
        p.Add(5, TBL__contents__content_assignedto);
        p.Add(6, TBL__contents__content_author_user_id);
        p.Add(7, TBL__contents__content_editor_user_id);
        p.Add(8, TBL__contents__content_order);
        p.Add(9, TBL__contents__content_date_modified);
        p.Add(10, TBL__contents__content_date_published);
        p.Add(11, TBL__contents__content_date_created);
        p.Add(12, TBL__contents__content_deleted);
        return p;
    }
    /// <summary>Get relevant columns for user.</summary>
    public static System.Collections.SortedList getColumnsRelevantPosts_VW() {
        System.Collections.SortedList p = new System.Collections.SortedList();

        p.Add(1, TBL__contents__content_id);
        p.Add(2, TBL__contents__content_title);
        p.Add(3, TBL__contents__content_status);
        p.Add(4, TBL__contents__content_parent_id);
        p.Add(5, TBL__contents__content_assignedto);
        p.Add(6, TBL__contents__content_author_user_id);
        p.Add(7, TBL__contents__content_editor_user_id);
        p.Add(8, TBL__contents__content_order);
        p.Add(9, TBL__contents__content_date_modified);
        p.Add(10, TBL__contents__content_date_published);
        p.Add(11, TBL__contents__content_date_created);
        p.Add(12, TBL__contents__content_deleted);
        return p;
    }

#region Stored Procedures

    /// <summary>Create (INSERT) database item using stored procedure.</summary>
    public int createContent(System.Collections.Hashtable parameters) {
        return (int)runStoredProcedure("spInsertContent", parameters, DatabaseCommon.enumSqlDataReturn.Integer);
    }
    /// <summary>
    /// Get (SELECT) database item using stored procedure.
    /// 
    /// <para>parameter @content_type</para>
    /// <para>parameter @content_parent_id</para>
    /// <para>parameter @content_assignedto</para>
    /// <para>parameter @content_title_slug</para>
    /// <para>parameter @content_deleted</para>
    /// <para>parameter @option__content_id___include_children Include the children with the content id.</para>
    /// <para>parameter @option__content_parent_id___include_parent Include the parent with the children.</para>
    /// <para>parameter @option__content_assignedto___session_user_id Filter assigned to by user id (session).</para>
    /// <para>parameter @option_select Custom view.</para>
    /// </summary>
    /// <remarks>Stored Procedure using Dynamic SQL</remarks>
    public DataTable getContents(System.Collections.Hashtable parameters) {
        return (DataTable)runStoredProcedure("spSelectContents_Dynamic", parameters, DatabaseCommon.enumSqlDataReturn.DataTable);
    }
    /// <summary>
    /// Get (SELECT) database item using stored procedure.
    /// 
    /// <para>parameter @content_assignedto</para>
    /// <para>parameter @content_deleted</para>
    /// <para>parameter @option__content_assignedto___session_user_id Filter assigned to by user id (session).</para>
    /// <para>parameter @limit Limit the total of records.</para>
    /// </summary>
    /// <remarks>Stored Procedure using Dynamic SQL</remarks>
    public DataTable getContentsRecent(System.Collections.Hashtable parameters) {
        return (DataTable)runStoredProcedure("spSelectContentsRecent_Dynamic", parameters, DatabaseCommon.enumSqlDataReturn.DataTable);
    }
    /// <summary>Get (SELECT) database item using stored procedure.</summary>
    public DataTable getContentsBySearch(System.Collections.Hashtable parameters) {
        return (DataTable)runStoredProcedure("spSelectContentsBySearch", parameters, DatabaseCommon.enumSqlDataReturn.DataTable);
    }
    /// <summary>Get (SELECT) database item using stored procedure.</summary>
    public DataTable getContentsHierarchy(System.Collections.Hashtable parameters) {
        return (DataTable)runStoredProcedure("spSelectContents_Hierarchy", parameters, DatabaseCommon.enumSqlDataReturn.DataTable);
    }
    /// <summary>Set (UPDATE) database item using stored procedure.</summary>
    public bool setContent(System.Collections.Hashtable parameters) {
        return (bool)runStoredProcedure("spUpdateContent", parameters, DatabaseCommon.enumSqlDataReturn.Boolean);
    }
    /// <summary>Set (UPDATE) database item using stored procedure.</summary>
    public bool setContent_Ajax(System.Collections.Hashtable parameters) {
        return (bool)runStoredProcedure("spUpdateContent_Ajax", parameters, DatabaseCommon.enumSqlDataReturn.Boolean);
    }

#endregion

#region Dynamic SQL

    /// <summary>Get contents by type for list (optional, exclude current content or passing integer zero to disable).</summary>
    public DataTable getContentsByTypeForList(TableLists.enumContentType type, int excludeContentId) {
        System.Collections.Hashtable p1 = new System.Collections.Hashtable();
        p1.Add(TBL__contents__content_id, null);
        p1.Add(TBL__contents__content_title, null);
        if(excludeContentId == 0) {
            return base.dynamicSqlSelect(p1, TableContents.TBL__contents, TableContents.TBL__contents__content_type + " = " + (int)type + " AND " + TableContents.TBL__contents__content_deleted + " = 0");
        } else {
            return base.dynamicSqlSelect(p1, TableContents.TBL__contents, TableContents.TBL__contents__content_type + " = " + (int)type + " AND " + TableContents.TBL__contents__content_id + " != " + excludeContentId + " AND " + TableContents.TBL__contents__content_deleted + " = 0");
        }
    }
    /// <summary>Get content.</summary>
    /// <returns>A System.Data.DataRow instance containing the match row.</returns>
    public DataRow getContent(int id) {
        return base.dynamicSqlSelect(null, TableContents.TBL__contents, TableContents.TBL__contents__content_id + " = " + id).Rows[0];
    }
    /// <summary>Get contents by type.</summary>
    /// <returns>A System.Data.DataTable instance containing the matching rows.</returns>
    public DataTable getContentsByType(TableLists.enumContentType type) {
        return base.dynamicSqlSelect(null, TableContents.TBL__contents, TableContents.TBL__contents__content_type + " = " + (int)type + " AND " + TableContents.TBL__contents__content_deleted + " = 0");
    }
    /// <summary>Get contents by two types and status.</summary>
    /// <returns>A System.Data.DataTable instance containing the matching rows.</returns>
    public DataTable getContentsByTypeAndStatus(TableLists.enumContentType type1, TableLists.enumContentType type2, TableLists.enumContentStatus status) {
        return base.dynamicSqlSelect(null, TableContents.TBL__contents, TableContents.TBL__contents__content_type + " IN (" + (int)type1 + ", " + (int)type2 + ") AND " + TableContents.TBL__contents__content_status + " = " + (int)status + " AND " + TableContents.TBL__contents__content_deleted + " = 0");
    }

#endregion

    /// <summary>Authenticate user by content (assigned to).</summary>
    public static bool authenticateUserByContent(bool pass, int contentAssignedTo, System.Web.SessionState.HttpSessionState userSession) {
        if((int)ApplicationCommon.getRole(userSession) > (int)TableLists.getRoleByAssignedTo(contentAssignedTo)) {
            pass = false;
        }
        if(contentAssignedTo == (int)userSession[ApplicationCommon.SESSION__user_id]) {
            pass = true;
        }
        return pass;
    }
    /// <summary>Get URLs from content.</summary>
    /// <returns>An array of System.String instance containing URLs.</returns>
    public String[] getFeedUrls(String content) {
        return ApplicationCommon.getStringInBetween("[[feed:", "]]", content, false, false);
    }
    /// <summary>To slug (URL friendly).</summary>
    /// <remarks>http://www.intrepidstudios.com/blog/2009/2/10/function-to-generate-a-url-friendly-string.aspx</remarks>
    public static String toSlug(string s) {
        s = s.ToLower();
        s = System.Text.RegularExpressions.Regex.Replace(s, @"[^a-z0-9\s-]", ""); // Invalid characters.        
        s = System.Text.RegularExpressions.Regex.Replace(s, @"\s+", " ").Trim(); // Convert multiple spaces into one space.
        s = s.Substring(0, s.Length <= 1024 ? s.Length : 1024).Trim(); // Cut and trim.
        s = System.Text.RegularExpressions.Regex.Replace(s, @"\s", "-"); // Convert each single space to a hyphen.
        return s;
    }

}

} // END namespace ent
